package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAsset;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.Query;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRDepartment;
import extend.org.compiere.utils.Util;

public class A_Asset_Form_Inv12 extends SvrProcess {
	/*Current context*/
	private Properties m_ctx ;
	
	private ProcessInfo pi ;
	
	Integer p_mol;
	
	Timestamp p_DatePreparation;
	
	String ResultPath;
	
	/*
	* Prepare - e.g., get Parameters.
	*/
	@Override
	protected void prepare() {
	log.info( "");
	m_ctx = Env. getCtx();
	pi = getProcessInfo();
	ProcessInfoParameter[] para = getParameter();
	for (int i = 0; i < para.length; i++) {
		String name = para[i].getParameterName();
		if (para[i].getParameter() == null)
			;
		else if (name.equals("mol"))
			p_mol = para[i].getParameterAsInt();
		else if (name.equals("DatePreparation"))
			p_DatePreparation = (Timestamp) para[i].getParameter();
		else
			log.log(Level. SEVERE, "Unknown Parameter: " + name);
		}
	}
	
	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_Inv12_" ;
		Integer pathI = 0;
		boolean flag = true ;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(" ;
					ResultPath = ResultPath .substring(0, 12) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx" ;
		MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate (m_ctx , "NotFoundTemplate"));

		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		Font mergeFont = wb.getFontAt(sheet.getRow(9).getCell(3).getCellStyle().getFontIndex());
		mergeStyle.setFont(mergeFont);
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);
		
		//Header
		sheet.getRow(8).getCell(0).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(8).getCell(35).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		
		sheet.getRow(20).getCell(8).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(p_DatePreparation)));
		String sqlheader = "SELECT HR_Department_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_mol;
		int id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(20).getCell(14).setCellValue((new MHRDepartment(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(20).getCell(23).setCellValue((new MBPartner(m_ctx, p_mol, get_TrxName()).getName()));
		
		
		//Positions
		int row = 32;
		StringBuffer sbuf = new StringBuffer();
		String whereClauseFinal = "AD_Client_ID = ?" 
					+ " AND AD_Org_ID = ?" 
					+ " AND C_BPartnerSR_ID = ?" 
					+ " AND IsActive = 'Y'";
		List<MAsset> assets = new Query(getCtx(), MAsset.Table_Name, whereClauseFinal, get_TrxName())
		.setParameters(Env.getAD_Client_ID(m_ctx), Env.getAD_Org_ID(m_ctx), p_mol)
		.list();
		
		for(MAsset asset: assets) {
			//Shifting and Merging
			sheet.shiftRows(row, row + 6, 1);
			sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 2));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 3, 7));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 8, 12));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 13, 19));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 20, 26));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 27, 31));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 32, 36));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 37, 41));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 42, 47));
			
			R = sheet.createRow(row);
			for(int i = 0; i <= 47; i++){
				C = R.createCell(i);
				C.setCellStyle(mergeStyle);
			}
			
//			sheet.getRow(row).getCell(0).setCellValue(++no);
//			sheet.getRow(row).getCell(3).setCellValue(Date);
			sheet.getRow(row).getCell(8).setCellValue(asset.getValue());
			sheet.getRow(row).getCell(13).setCellValue(asset.getName());
			sheet.getRow(row).getCell(20).setCellValue(asset.isOwned() == true ? "собственный" : "арендованный");
			sheet.getRow(row).getCell(27).setCellValue(asset.getAssetCost(p_DatePreparation).doubleValue());
			
			sbuf.setLength(0);
			sbuf.append("select * from A_InternalMovement " 
						+ " WHERE a_asset_ID = " + asset.get_ID() 
						+ " and datemoved = ( " 
							+ " select max(datemoved) " 
							+ " from A_InternalMovement " 
							+ " where a_asset_id = " + asset.get_ID() 
							+ " and datemoved <= '" + p_DatePreparation + "')");
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = DB.prepareStatement(sbuf.toString(), get_TrxName());
				rs = ps.executeQuery();
				if (rs.next()) {
					sheet.getRow(row).getCell(32).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(rs.getDate("datemoved"))));
					sheet.getRow(row).getCell(37).setCellValue(rs.getString("documentno"));
					sheet.getRow(row).getCell(42).setCellValue(rs.getString("description"));
				}
			} catch (Exception e) {
				log.log(Level.SEVERE, sbuf.toString(), e);
			} finally {
				DB.close(rs, ps);
				rs = null;
				ps = null;
			}
					
			row++;
		}
		

		//--------------------------------------------------------------------------
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util.launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}	
}
